package 数据库链接;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

public class JDBCMains
{
	public static void main(String[] args)
	{
		System.out.println("==");
		/*查询列表中所有记录*/
		//sy();
		/*查询列表中一条记录*/
		//yt(1);
		/*添加一条信息*/
		//tj("隋朝",29,"长安",new Date());
		/*更新一条新的记录*/
		//gx(7,"西周",324,"西岐", new Date());
		/*删除一条记录*/
		//delete(6);
		/*通过条件查询*/
		tj("唐代","长安");
	}
	/**
	 *删除一条记录
	 *DELETE FROM `my`.`a_chaodai` WHERE `id` = 8
	 */
	private static void delete(int id)
	{
		/*装驱动*/
		String d = "com.mysql.jdbc.Driver";
		/*设置 登录 路径*/
		String url = "jdbc:mysql:///my?useSSL=false&user=root&password=123456";
		/*删除的sql句*/
		String sql = "DELETE FROM `my`.`a_chaodai` WHERE `id` = "+id;
		Connection con = null ;
		Statement stat = null ; 
		ResultSet res = null ;
		try
		{
			/*开始驱动*/
			Class.forName(d);
			con = DriverManager.getConnection(url);
			stat = con.createStatement();
			/*记录影响了多少条记录*/
			int s = stat.executeUpdate(sql);
			System.out.println("=="+s);
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			try
			{
				if(res != null)
				{
					res.close();
					res = null;
				}
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try
			{
				if(stat !=null)
				{
					stat.close();
					stat = null ;
				}
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try
			{
				if(con !=null)
				{
					con.close();
					con = null;
				}
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	/**
	 * 更新一条新的记录
	 * */
	private static void gx(int id ,String chaodai , int guozuo ,String shoudu , Date time)
	{
		/*时间格式化*/
		SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd HH:mm:ss");
		String  shijian = sdf.format(time);
		/*加载驱动*/
		String d = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql:///my?useSSL=false";
		String user = "root";
		String password = "123456";
		String sql = "UPDATE `my`.`a_chaodai` SET `chaodai` = '"+chaodai+"', `guozuo` = "+guozuo+", `shoudu` = '"+shoudu+"', `time` = '"+shijian+"' WHERE `id` = "+id;
		Connection con = null ;
		Statement stat = null ; 
		ResultSet res = null ;
		try
		{
			/*驱动开始*/
			Class .forName(d);
			con = DriverManager.getConnection(url, user, password);
			stat = con.createStatement();
			/*此处的返回值是sql语句影响了多少条记录*/
			int gengxin = stat.executeUpdate(sql);
			System.out.println("=="+gengxin);
		} catch (ClassNotFoundException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			try
			{
				if(res != null)
				{
					res.close();
					res=null;
				}
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try
			{
				if(stat != null)
				{
					stat.close();
					stat=null;
				}
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try
			{
				if(con != null)
				{
					con.close();
					con=null;
				}
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	/**
	 * 在数据库中
	 * 添加一条信息
	 * */
	private static void tj(String chaodai , int guozuo , String shoudu ,Date time)
	{
		/*时间格式化
		SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd HH:mm:ss");
		String  shijian = sdf.format(time);*/
		/*设置路径*/
		String d = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql:///my?useSSL=false";
		String user = "root";
		String password = "123456";
		String sql = "INSERT INTO `my`.`a_chaodai`(`chaodai`, `guozuo`, `shoudu`, `time`) VALUES ('"+chaodai+"',"+guozuo+", '"+shoudu+"', now())";
		Connection con = null ;
		Statement stat = null ; 
		ResultSet res = null ;
		try
		{
			/*驱动开始*/
			Class .forName(d);
			con = DriverManager.getConnection(url, user, password);
			stat = con.createStatement();
			/*此处的返回值是sql语句影响了多少条记录*/
			int ser = stat.executeUpdate(sql);
			System.out.println("=="+ser);
		} catch (ClassNotFoundException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			try
			{
				if(res != null)
				{
					res.close();
					res=null;
				}
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try
			{
				if(stat != null)
				{
					stat.close();
					stat=null;
				}
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try
			{
				if(con != null)
				{
					con.close();
					con=null;
				}
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	/**
	 * 查询一条记录*/
	private static void yt(int idcond)
	{
		/*安装驱动器*/
		String b = "com.mysql.jdbc.Driver";
		/*加载路径*/
		String url = "jdbc:mysql:///my?useSSL=false&user=root&password=123456";
		/*创建sql 语句*/
		String sql = "select*from a_chaodai where id= "+ idcond;
		Connection con = null ;
		Statement stat = null ;
		ResultSet res = null ; 
		/*创建一个对象*/
		try
		{	
			/*加载驱动*/
			Class.forName(b);
			/*链接数据库*/
			con = DriverManager.getConnection(url);
			/*中转站*/
			stat = con.createStatement();
			/*获取结果集*/
			res = stat .executeQuery(sql);
			/*根据ID 查询结果
			 * 当查询一条结果的时候就
			 * 使用if就可以了*/
			if(res.next())
			{
				int id = res.getInt(1);
				String chaodai = res.getString("chaodai");
				int guozuo = res.getInt("guozuo");
				String shoudu = res.getString("shoudu");
				Date createTime = res.getTimestamp("time");
				System.out.println(id+"\t"+chaodai+"\t"+guozuo+"\t"+shoudu+"\t"+createTime);
			}
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally 
		{
			try
			{
				if(res !=null)
				{
					res.close();
					res = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(stat !=null)
				{
					stat.close();
					stat = null ;
				}	
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try
			{
				if(con !=null)
				{
					con.close();
					con = null;
				}
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}
	/**
	 *查询所有记录
	 */
	private static void sy()
	{
		// TODO Auto-generated method stub
		
		
		/*驱动的类名*/
		String d = "com.mysql.jdbc.Driver";
		/*这三个字符串填写的是
		 * IP
		 * 端口
		 * 用户名
		 * 密码
		 */
		String url = "jdbc:mysql:///my?useSSL=false";
		String user = "root";
		String password = "123456";
		/*创建SQL句*/
		String sql = "select*from a_chaodai";
		/*创建一个对象 不用new*/
		/*获取一个驱动链接*/
		Connection con =null;
		Statement  stat = null ;
		ResultSet  res = null ; 
		try
		{
			Class cla = Class.forName(d);
			con = DriverManager.getConnection(url, user, password);
			stat = con.createStatement();
			res = stat.executeQuery(sql);
			/*把ResultSet 当成迭代器是使用*/
			System.out.println("id\tchaodai\tguozuo\tshoudu\ttime");
			while(res.next())
			{
				/*要从数据库中取记录*/
				int id = res.getInt(1);
				String chaidai = res.getString("chaodai");
				int guozuo = res.getInt("guozuo");
				String shoudu = res.getString("shoudu");
				/*转换时间格式*/
				Date createTime = res.getTimestamp("time");
				System.out.println(id+"\t"+chaidai+"\t"+guozuo+"\t"+shoudu+"\t"+createTime.toLocaleString());
			}
		} catch (ClassNotFoundException e)
		{
			
			e.printStackTrace();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			try
			{
				if(res != null)
				{
					res.close();
					res = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(stat != null)
				{
					stat.close();
					stat = null ;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(con !=null)
				{
					con.close();
					con = null ; 
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
	}
	/**
	 * 预处理 应用
	 */
	private static void tj(String chao ,String shou)
	{
		/*四步走*/
		String d = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql:///my?useSSL=false";
		String user = "root";
		String password = "123456";
		/*运用占位符
		 * select *from a_chaodai where chaodai='唐代' and shoudu='长安'
		 * */
		String sql = "select *from a_chaodai where chaodai= ? and shoudu= ?;";
		Connection con = null ;
		PreparedStatement  ps = null ;
		ResultSet  res = null ;
		try
		{
			/*驱动链接*/
			Class.forName(d);
			con=DriverManager.getConnection(url, user, password);
			/*要预处理 就要把sql句先放进去*/
			ps = con.prepareStatement(sql);
			/*预处理之后 执行sql句之前要对其进行赋值*/
			ps.setString(1,chao);
			ps.setString(2,shou);
			/*执行的时候就不用再传sql句了*/
			res= ps.executeQuery();
			if(res.next())
			{
				/*查询到结果了*/
				System.out.println("=找到结果=");
				/*在数据库中取值*/
				int id = res.getInt("id");
				String chaodai= res.getString("chaodai");
				int guozuo = res.getInt("guozuo");
				String shoudu = res.getString("shoudu");
				Date time = res .getTimestamp("time");
				/*输出查询的结果*/
				System.out.println(id+"\t"+chaodai+"\t"+guozuo+"\t"+shoudu+"\t"+time);
			}else
			{
				System.out.println("=没有找到结果=");
			}
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
